The great flexibility of the ADO programming model is apparent from the very first action you perform in every database-oriented application. that is, setting up a connection to your database. In fact, while the most obvious choice is to create a stand-alone Connection object, it surely isn't the only available one. For example, you can also create a stand-alone Recordset object and assign a connection string to its Open method. Or you can create a stand-alone Command object and assign a connection string to its ActiveConnection property. You don't even need to create an ADO object to establish the connection, if you use the ADO Data control or the DataEnvironment designer.
Regardless of the object you use to connect to an ADO data source, you must build a connection string. This will be the string that you assign to the ConnectionString property of a Connection object or to the ActiveConnection property of a Recordset or Command object, or it will be the string that you pass to the Execute method of a Connection object or to the Open method of a Recordset object. So you need to understand how to correctly build this string and how to exploit all the options you have.
The connection string can contain one or more elements in the form argument=value. The list of valid arguments depends on the provider to which you're connecting, but two arguments are always supported: the Provider and the File Name. For a list of supported arguments, see Table 13-1 in Chapter 13.
Determining which arguments are valid with which provider is the most difficult job in building a connection string. When I began to explore this unknown territory, I found that the best approach was to drop an ADO Data control on a form, set a connection using its property pages, and then see the final value of the ConnectionString property. I also discovered that I could modify the values in the All property page, and my settings were reflected in the ConnectionString property.
The simplest way to connect to any database is based on Microsoft Data Link files (I explained how to create these files in "The Binding Mechanism" section in Chapter 8). The UDL file contains all the information needed by ADO to connect to the database, including the name of the provider, and you only need to use the File Name argument. In fact, if you use both the Provider and File Name arguments, you get an error. The following example assumes that you have prepared a UDL file that points to the Biblio.mdb database:
Dim cn As New ADODB.Connection
cn.Open "File Name=C:\Program Files\Common Files\System\ole db" _
& "\Data Links\Biblio.udl"
|
You can also use the File Name argument to point to a file-based DSN, in which case you're implicitly using the default MSDASQL provider for ODBC sources:
cn.Open "File Name=C:\Program Files\Common Files\ODBC\Data Sources\" _
& "Pubs.dsn"
|
If you're using a user or system ODBC Data Source Name, you simply have to specify its name with a Data Source or DSN argument. The following code assumes that you've created a system or user DSN that points to the Pubs database on Microsoft SQL Server and shows how you can open a Recordset without first creating an explicit Connection object:
Dim rs As New ADODB.Recordset rs.Open "Authors", "Provider=MSDASQL.1;User ID=sa;Data Source=Pubs" ' You can omit the name of the provider because it's the default. rs.Open "Authors", "DSN=Pubs" |
If you aren't using DSNs or UDL files, you must build the connection string yourself. This is the ADO equivalent of a DSN-less connection: On one hand, it simplifies the installation of the application (because you don't have to create the DSN or UDL file on your customer's machine); on the other, it makes the programmer's job a little harder. When connecting to a Microsoft Jet database, you need only the name of the provider and the path of the MDB database:
cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=E:\Microsoft Visual Studio\VB98\Biblio.mdb
|
The job is a bit more complex when you're connecting to an SQL Server database either through the dedicated OLE DB provider or through the default MSDASQL provider. This is the connection string to connect to the Pubs database on the SQL Server located on the MyServer workstation, using Windows NT integrated security:
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& " Data Source=MyServer;Initial Catalog=Pubs;"
|
In this case, Data Source is the name of the server, and you specify that you want to use integrated security by setting the Integrated Security argument to the SSPI value. The following statement opens a connection to the same SQL Server database, this time using an explicit user ID and password:
cn.Open "Provider=SQLOLEDB.1;Data Source=MyServer;User ID=sa;" _
& "Password=mypwd;Initial Catalog=Pubs"
|
Connection Timeout is another handy connection string argument. You usually don't need it when you're opening a connection using a Connection object because this object exposes the ConnectionTimeout property that lets you set a timeout when opening the connection. You do need it, however, when you create an implicit Connection object in the Open method of a Recordset or the Execute method of a Command:
rs.Open "Authors", "Provider=SQLOLEDB.1;Data Source=MyServer;User ID=sa;" _
& "Connection Timeout=10;Initial Catalog=Pubs"
|
When you're working with an SQL Server source, you can use many additional arguments to fine-tune your connection. For example, the PacketSize argument sets the size of data block sent through the network. (The default is 4096 bytes.) The Use Procedure for Prepare argument specifies whether ADO should create stored procedure by default. The possible values are 0=No, 1-Yes And Drop Them When You Disconnect (the default), or 2=Yes And Drop Them When You Disconnect And As Appropriate While You're Connected. The Locale Identifier argument sets the international locale. The Network Address and Network Library arguments should be specified when you're accessing SQL Server with a method other than named pipes; the Workstation ID argument identifies the machine from which you're connecting.
If you're connecting through the default MSDASQL provider, you must specify several additional arguments, the most important of which is the ODBC driver that you want to use:
cn.ConnectionString = "Provider=MSDASQL.1;User ID=sa;" _
&"ExtendedProperties=""DRIVER= SQL Server;SERVER=ServerNT;"_
&"MODE=Read;WSID=P2;DATABASE=pubs"""
|
As you see, when working with the OLE DB Provider for ODBC you can insert the same ODBC argument list you used with RDO by enclosing it between double quotes and assigning it to the Extended Properties argument. This string is passed to the provider without ADO trying to interpret it. When you're using this argument in a Visual Basic statement, as in the previous statement, you must use two consecutive double quotes characters. As I mentioned previously, however, you can also pass arguments using the old ODBC syntax and ADO interprets them correctly anyway:
' You can omit the Provider argument because you're using MSDASQL.
cn.ConnectionString = "DRIVER={SQL Server};SERVER=
MyServer;"_&"UID=sa;DATABASE=pubs"
|
You can pass a user's name and password using either the ADO syntax (User Id and Password arguments) or the ODBC syntax (UID and PWD arguments). If you pass both, the ADO syntax wins.
When working with the Microsoft Jet OLE DB Provider, you can pass additional login information, either in the connection string or as dynamic properties of the Connection object. Jet OLEDB:System Database is the path and name of the file with the information on the workgroup, Jet OLEDB:Registry Path is the Registry key that holds values for the Jet engine, and Jet OLEDB:Database Password is the database password:
cn.Properties("Jet OLEDB:Database Password") = "mypwd"
|
After you have built a correct connection string, the operations you must perform to actually open the connection depend on the object you want to use.
Most often you build a connection using a stand-alone Connection object, which you can reuse for all the queries and commands on that data source during the life of your application. You should assign reasonable values to the Connection object's Mode and ConnectionTimeout properties and also to its Provider property (unless the connection string contains the Provider or File Name argument):
' Prepare to open a read-only connection. Dim cn As New ADODB.Connection cn.ConnectionTimeout = 30 ' Default for this property is 15 seconds. cn.Mode = adModeRead ' Default for this property is adModeUnknown. |
At this point, you can choose from several ways to open the connection. You can assign the connection string to the ConnectionString property and then invoke the Open method:
cn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=MyServer;" _
& "Initial Catalog=Pubs"
' The second and third arguments are the user name and the password.
cn.Open , "sa", "mypwd"
|
Alternatively, you can pass the connection string as the first argument to the Open method. In the following example, the user name and the password are embedded in the connection string, so you shouldn't specify them as separate arguments. (If you do so, the results are unpredictable.)
cn.Open "Provider=SQLOLEDB.1;Data Source=MyServer;User ID=sa;" _
& "Password=mypwd;Initial Catalog=Pubs"
|
You should know one more thing about the ConnectionString property: If you assign a value to it and then open the connection, reading its value back will probably return a different string, one that often contains many additional values put there by the provider. This is perfectly normal. When you eventually close the Connection object, the ConnectionString property will be restored to the value that you originally assigned to it.
From time to time, you might prefer not to create a stand-alone Connection object and instead directly use a Recordset or Command object. This is mostly a matter of programming style, however, because even if you don't explicitly create a Connection object, ADO does it for you. So you aren't actually saving any resources, either on the server or the client workstation. The only savings you get is in terms of lines of code. For example, you need only two statements to execute an SQL query on any database:
Dim rs As New ADODB.Recordset
rs.Open "Authors", "Provider=SQLOLEDB.1;Data Source=MyServer;User ID=sa;" _
& "Password=mypwd;Initial Catalog=Pubs"
|
You can use a similar technique to open an implicit connection with a Command object, but in this case you need to write more code because you must set the ActiveConnection and CommandText properties before opening the connection and carrying out command with the Execute method, as you can see in the code here.
Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=
MyServer;" _&"userID=sa;Password=mypwd;Initial Catalog=Pubs"
cmd.CommandText = "DELETE FROM Authors WHERE State = 'WA'"
cmd.Execute
|
When you open a connection with a Recordset or Command object in one of the ways described previously, you can access the implicit Connection object that ADO creates for you by querying the Recordset's or Command's ActiveConnection property, as in the following code snippet:
' Display the errors in the connection created by the previous example.
Dim er As ADODB.Error
For Each er In cmd.ActiveConnection.Errors
Debug.Print er.Number, er.Description
Next
|
When you open an implicit Connection object using a Recordset or Command object, you inherit all the default values for the Connection object's properties. This is often too limiting and is a good reason to prefer explicit Connection objects. By default, ADO uses a ConnectionTimout equal to 15 seconds and creates server-side, forward-only, and read-only cursors with CacheSize=1 (also called noncursors).
I haven't yet covered one important aspect of the Connection object: What happens if the information in the connection string isn't sufficient to set up the connection? If you're working with the standard OLE DB provider for an ODBC source, this behavior can be controlled by the dynamic Prompt property, which appears in the Connection's Properties collection. This property can be set to the following values: 1adPromptAlways (always displays the login dialog box), 2-adPromptComplete (displays the login dialog box only if some required values are missing in the connection string), 3-adPromptCompleteRequired (similar to adPromptComplete, but the user can't enter any optional value), and 4-adPromptNever (never displays the login dialog box). The default value for this property is adPromptNever: If the connection string doesn't include enough information to carry out the operation, no login dialog box is displayed and the application receives an error. Use the following code to change this default behavior:
' Display the login dialog box if necessary.
Dim cn As New ADODB.Connection
cn.Properties("Prompt") = adPromptComplete
cn.Open "Provider=MSDASQL.1;Data Source=Pubs"
|
The Prompt dynamic property works in this way with the MSDASQL provider only.
The Properties collection contains many other interesting bits of information. For example, your application can determine the name and the version of the database it's working with by using the DBMS Name and DBMS Version dynamic properties and the name of the server by using the Data Source Name property. Another group of properties returns information about the provider: Provider Name returns the name of the DLL, Provider Friendly Name is the string that you see when selecting the provider from the list of all the OLE DB providers installed on the machine, and Provider Version is a string that identifies its version. You might want to print this information to a log file if your application isn't working properly on a customer's machine.
All the connection examples you've seen so far have one thing in common: They're performed synchronously. This means that the Visual Basic program won't execute the statement that follows the Open method until the connection is established, the connection times out, or another error occurs. In the meantime, the application won't respond to a user's actions, which is a situation that you should avoid, especially if you set a high value for the ConnectionTimeout property.
Fortunately, ADO solves this problem in a simple and elegant way. In fact, you can keep the Visual Basic program from waiting by passing the adAsyncConnect value to the Options argument of the Open method. When you open a connection asynchronously, you must determine when the connection is ready (or when an error occurs). This can be achieved in two ways: by polling the Connection's State property or by using events. Polling the State property is the simplest solution, but it's often inadequate if you need to perform complex operations while the connection is being attempted:
Dim cn As New ADODB.Connection
On Error Resume Next
cn.Open "Provider=sqloledb;Data Source=MyServer;Initial Catalog=pubs;" _
&"UserID=sa;Password=;", , , adAsyncConnect
' State is a bit-field value, so you need the And operator to test one bit.
Do While (cn.State And adStateConnecting)
' Perform your operations here.
...
' Let the user interact with the program's user interface.
DoEvents
Loop
' Check whether the connection has been successful.
If cn.State And adStateOpen Then MsgBox "The connection is open."
|
A better solution is to use the ConnectComplete event. You declare the Connection object variable using the WithEvents keyword and create a new instance of it when you're ready to open the connection, as you can see in the code below.
Dim WithEvents cn As ADODB.Connection
Private Sub cmdConnect_Click()
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 20
cn.Open "Provider=sqloledb;Data Source=MyServer;" _
& "Initial Catalog=pubs;", "sa", , adAsyncConnect
End Sub
Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As _
ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
If adStatus = adStatusOK Then
MsgBox "The connection is open"
ElseIf adStatus = adStatusErrorsOccurred Then
MsgBox "Unable to open the connection" & vbCr & Err.Description
End If
End Sub
|
The Connection object also fires the WillConnect event, though its usefulness is limited. For example, you can use it to modify the connection string to specify the provider you're connecting to (instead of modifying the string in multiple points of the application's source code), or you can give users the ability to select a server, enter their password, and so on:
Private Sub cn_WillConnect(ConnectionString As String, UserID As String, _
Password As String, Options As Long, adStatus As _
ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
If UserID <> "" And Password = "" Then
' Ask for user's password.
Password = InputBox("Please enter your password")
If Password = "" Then
' If not provided, cancel the command if possible.
If adStatus <> adStatusCantDeny Then adStatus = adStatusCancel
End If
End If
End Sub
|
When you're working with the WillConnect event, keep in mind that its parameters exactly match the values assigned to the corresponding arguments of the Open method. This implies, for example, that the UserID and Password parameters both receive an empty string if the user name and password were passed in the connection string. If you set the adStatus parameter to the adStatusCancel value, an error is returned to the Open method and the connection isn't even attempted.
The WillConnect and ConnectComplete events fire when the connection isn't opened in asynchronous mode, and the ConnectComplete fires also if you canceled the operation in the WillConnect event. In this case, the ConnectComplete event receives adStatus set to adStatusErrorsOccurred and pErrorNumber set to error 3712, "Operation has been canceled by the user."